/*
    Procedure:
    Check to see if STATUS = 'A' for valid records.

    DIRECTIONS:  The site pulls the column data dynamically from the metadata.
    It requires the ID as "dbid" column in there; the other columns are at the
    discretion of the stored procedure programmer.
*/
CREATE PROCEDURE [dbo].[BAERosterManagementAvailableRecords]
      @UID VARCHAR(10),
      @sortColumn VARCHAR(300),
      @isAsc tinyint
AS
      DECLARE @COID VARCHAR(50);
      DECLARE @columnSortDirection VARCHAR(30);
      DECLARE @sql NVARCHAR(1000);

      SET @COID = (SELECT CO_ID FROM Name WHERE ID = @UID);
      IF (@COID = '')
      BEGIN
              SET @COID = (SELECT ID FROM Name WHERE ID = @UID);
      END

      IF (@isAsc = 1)
      BEGIN
            SET @columnSortDirection = 'ASC';
      END
      ELSE
      BEGIN
            SET @columnSortDirection = 'DESC';
      END

      SET @sql = 'SELECT ID as "dbid", FIRST_NAME as "First Name", LAST_NAME as "Last Name",  MTT.DESCRIPTION as "Member Type", CITY as "City", STATE_PROVINCE as "State" FROM Name as NT JOIN Member_Types as MTT ON NT.MEMBER_TYPE = MTT.MEMBER_TYPE WHERE CO_ID = ' + @COID + ' AND STATUS = ''A'' AND NT.COMPANY_RECORD != 1 ORDER BY ' + @sortColumn + ' ' + @columnSortDirection;

      EXEC sp_executesql @sql













GO